package com.data.migration.service;

import com.data.migration.entity.Operation;
import com.data.migration.util.DBUtil;
import com.data.migration.vo.TeacherVo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Peter on 2018/12/8.
 * 迁移1v1课程数据
 */
public class Step_3_Teacher {
    public static final String step = "3";
    public static final String stepDesc = "老师";

    private final Logger logger = LoggerFactory.getLogger(Step_3_Teacher.class);
    public List<TeacherVo> teachers = new ArrayList<>();
    public List<Operation> insertSql = new ArrayList<>();//插入课程的sql

    public static void main(String[] args) throws SQLException {
        Step_3_Teacher step = new Step_3_Teacher();
        step.start();
//        step.clean();

    }

    public void start() throws SQLException {
        logger.info("\n******************************************************************************" +
                "\n======================== Step 3: 迁移老师 start ================================" +
                "\n******************************************************************************\n");
        // 3.1. 老师【td_nt_teacher】
        step_1();
        // 3.2. 老师认证信息 【td_nt_authentication】
        step_2();
        step_2_2();
        // 3.3. 老师开课 【td_nt_teach_course】
        step_3();
        step_3_2();
        step_3_3();
        logger.info("\n******************************************************************************" +
                "\n======================== Step 3: 迁移老师 end ================================" +
                "\n******************************************************************************\n");
    }

    public void clean() throws SQLException {
        logger.info("\n******************************************************************************" +
                "\n======================== 【clean】 Step 3: 迁移老师 start =========================" +
                "\n******************************************************************************");
        step_1_clean();// 3.1. 老师【td_nt_teacher】
        step_2_clean();// 3.2. 老师认证信息 【td_nt_authentication】
        step_3_clean();// 3.3. 老师开课 【td_nt_teach_course】
        logger.info("\n******************************************************************************" +
                "\n======================== 【clean】 Step 3: 迁移老师 end =========================" +
                "\n******************************************************************************");
    }


    // 3.1. 老师【td_nt_teacher】     ------------------------------------------------------------------------------1
    public void step_1() throws SQLException {
        logger.info("\n     --------- 3.1 老师【td_nt_teacher】 start --------- ");
        // 查询出青少版所有的老师
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_teacher"; // 表名
        String pk = "TEA_ID"; // 主键（自增）的列名字
        teachers = DBUtil.buildInsertSqlForTeacher(conn, tableName, pk);

        // 更新或插入（如果A版有账号就更新，否则插入）
        DBUtil.migrationForTeacher(teachers);
    }
    public void step_1_clean() throws SQLException {
        logger.info("\n     ---------【clean】 3.1 老师【td_nt_teacher】 start --------- ");
        // 1. 删除新添加的老师
        String sql = "delete from td_nt_teacher where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);

        // 2. 已有账号的回复余额，设置t_id为NULL
        String updateSql = "update td_nt_teacher set TEA_BALANCE = TEA_BALANCE - t_tea_balance, t_id=NULL,t_tea_balance=0 where t_id is not NULL";
        DBUtil.executeUpdate(conn, updateSql);

    }

    // 3.2. 老师认证信息 【td_nt_authentication】     -------------------------------------------------------------------------------2
    public void step_2() throws SQLException {
        logger.info("\n     --------- 3.2 老师认证信息 【td_nt_authentication】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_authentication"; // 表名
        String pk = "auh_id"; // 主键（自增）的列名字
        insertSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertSql);
    }
    // 3.2.2. 更新外键【td_nt_authentication.tea_id】
    public void step_2_2() throws SQLException {
        String ftableName = "td_nt_authentication"; // 附表表名
        String fk = "tea_id";// 附表外键
        String ptableName = "td_nt_teacher";//主表的表名
        String pk = "tea_id"; // 主表的主键
        String sql = DBUtil.createUpdateFkSql(ftableName, fk, ptableName, pk);
        Connection conn = DBUtil.getConn(Operation.app_type_A);

        DBUtil.executeUpdate(conn, sql);
    }
    public void step_2_clean() throws SQLException {
        logger.info("\n     ---------【clean】 3.2 老师认证信息 【ts_nt_setmeal】 start --------- ");
        String sql = "delete from td_nt_authentication where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

    // 3.3. 老师开课 【td_nt_teach_course】     -------------------------------------------------------------------------------3
    public void step_3() throws SQLException {
        logger.info("\n     --------- 3.3 老师开课信息 【td_nt_teach_course】 start --------- ");
        Connection conn = DBUtil.getConn(Operation.app_type_T);
        String tableName = "td_nt_teach_course"; // 表名
        String pk = "TC_ID"; // 主键（自增）的列名字
        insertSql = DBUtil.buildInsertSql(conn, tableName, pk, step, stepDesc);

        DBUtil.insertBatch(insertSql);
    }
    // 3.3.2. 更新外键【td_nt_teach_course.TEA_ID】
    public void step_3_2() throws SQLException {
        String ftableName = "td_nt_teach_course"; // 附表表名
        String fk = "TEA_ID";// 附表外键
        String ptableName = "td_nt_teacher";//主表的表名
        String pk = "tea_id"; // 主表的主键
        String sql = DBUtil.createUpdateFkSql(ftableName, fk, ptableName, pk);
        Connection conn = DBUtil.getConn(Operation.app_type_A);

        DBUtil.executeUpdate(conn, sql);
    }
    // 3.3.2. 更新外键【td_nt_teach_course.cou_ID】
    public void step_3_3() throws SQLException {
        String ftableName = "td_nt_teach_course"; // 附表表名
        String fk = "TEA_ID";// 附表外键
        String ptableName = "ts_nt_course_info";//主表的表名
        String pk = "cou_ID"; // 主表的主键
        String sql = DBUtil.createUpdateFkSql(ftableName, fk, ptableName, pk);
        Connection conn = DBUtil.getConn(Operation.app_type_A);

        DBUtil.executeUpdate(conn, sql);
    }
    public void step_3_clean() throws SQLException {
        logger.info("\n     ---------【clean】 3.3 老师开课信息 【td_nt_teach_course】 start --------- ");
        String sql = "delete from td_nt_teach_course where app_type='T'";
        Connection conn = DBUtil.getConn(Operation.app_type_A);
        DBUtil.executeUpdate(conn, sql);
    }

}
